{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [],
   "source": [
    "import os\n",
    "import numpy as np\n",
    "import pandas as pd\n",
    "import lightgbm as lgb\n",
    "from sklearn.metrics import roc_auc_score\n",
    "from sklearn.preprocessing import LabelEncoder\n",
    "from sklearn.model_selection import StratifiedKFold"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [],
   "source": [
    "train_path = '../../../contest/train/'\n",
    "stage_path = '../../../contest/B榜/'\n",
    "stage = 'B'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_train = pd.read_csv('../../../contest/train/DZ_TARGET_TRAIN.csv')\n",
    "df_test = pd.read_csv('../../../contest/B榜/DZ_TARGET_TESTB.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_train_user = pd.DataFrame({'CUST_NO':df_train.CUST_NO})\n",
    "df_test_user = pd.DataFrame({'CUST_NO':df_test.cust_no})"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# all"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## IBTF和TPAY相加"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [],
   "source": [
    "ibtf_train = pd.read_csv(os.path.join(train_path,'DZ_TR_IBTF.csv'))\n",
    "ibtf_test = pd.read_csv(os.path.join(stage_path,f'DZ_TR_IBTF_{stage}.csv'))\n",
    "\n",
    "tpay_train = pd.read_csv(os.path.join(train_path,'DZ_TR_TPAY.csv'))\n",
    "tpay_test = pd.read_csv(os.path.join(stage_path,f'DZ_TR_TPAY_{stage}.csv'))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [],
   "source": [
    "ibtf_train = ibtf_train.drop('DATA_DAT',axis=1)\n",
    "ibtf_test = ibtf_test.drop('DATA_DAT',axis=1)\n",
    "\n",
    "tpay_train = tpay_train.drop('DATA_DAT',axis=1)\n",
    "tpay_test = tpay_test.drop('DATA_DAT',axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>CUST_NO</th>\n",
       "      <th>IBTF_MOTH_TR_AMT</th>\n",
       "      <th>IBTF_YEAR_TR_AMT</th>\n",
       "      <th>IBTF_MOTH_NET_TR_AMT</th>\n",
       "      <th>IBTF_YEAR_NET_TR_AMT</th>\n",
       "      <th>IBTF_MOTH_TR_AMT_IN</th>\n",
       "      <th>IBTF_YEAR_TR_AMT_IN</th>\n",
       "      <th>IBTF_MOTH_TR_CNT</th>\n",
       "      <th>IBTF_YEAR_TR_CNT</th>\n",
       "      <th>IBTF_MOTH_TR_CNT_IN</th>\n",
       "      <th>IBTF_YEAR_TR_CNT_IN</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>3e713ad9df87cfb7b987de3812a45652</td>\n",
       "      <td>37.952940</td>\n",
       "      <td>104.616867</td>\n",
       "      <td>37.952940</td>\n",
       "      <td>104.616867</td>\n",
       "      <td>37.952940</td>\n",
       "      <td>104.616867</td>\n",
       "      <td>5.0</td>\n",
       "      <td>33.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>33.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>266d637b68b11ae025d691c9f275eaa4</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>3.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>3.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>622058444bb91fc6e1a9ebbe84f5e9d0</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>3.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>3.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>b13325ac7f39ba579d97fbe0f2a6f8c8</td>\n",
       "      <td>15.259837</td>\n",
       "      <td>94.719645</td>\n",
       "      <td>15.259837</td>\n",
       "      <td>94.719645</td>\n",
       "      <td>15.259837</td>\n",
       "      <td>94.719645</td>\n",
       "      <td>5.0</td>\n",
       "      <td>53.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>53.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>b1bb863c52d6b383b01967706de44b55</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>3.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>3.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                            CUST_NO  IBTF_MOTH_TR_AMT  IBTF_YEAR_TR_AMT  \\\n",
       "0  3e713ad9df87cfb7b987de3812a45652         37.952940        104.616867   \n",
       "1  266d637b68b11ae025d691c9f275eaa4          0.000000          0.000000   \n",
       "2  622058444bb91fc6e1a9ebbe84f5e9d0          0.000000          0.000000   \n",
       "3  b13325ac7f39ba579d97fbe0f2a6f8c8         15.259837         94.719645   \n",
       "4  b1bb863c52d6b383b01967706de44b55          0.000000          0.000000   \n",
       "\n",
       "   IBTF_MOTH_NET_TR_AMT  IBTF_YEAR_NET_TR_AMT  IBTF_MOTH_TR_AMT_IN  \\\n",
       "0             37.952940            104.616867            37.952940   \n",
       "1              0.000000              0.000000             0.000000   \n",
       "2              0.000000              0.000000             0.000000   \n",
       "3             15.259837             94.719645            15.259837   \n",
       "4              0.000000              0.000000             0.000000   \n",
       "\n",
       "   IBTF_YEAR_TR_AMT_IN  IBTF_MOTH_TR_CNT  IBTF_YEAR_TR_CNT  \\\n",
       "0           104.616867               5.0              33.0   \n",
       "1             0.000000               3.0               3.0   \n",
       "2             0.000000               3.0               3.0   \n",
       "3            94.719645               5.0              53.0   \n",
       "4             0.000000               3.0               3.0   \n",
       "\n",
       "   IBTF_MOTH_TR_CNT_IN  IBTF_YEAR_TR_CNT_IN  \n",
       "0                  5.0                 33.0  \n",
       "1                  3.0                  3.0  \n",
       "2                  3.0                  3.0  \n",
       "3                  5.0                 53.0  \n",
       "4                  3.0                  3.0  "
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ibtf_test.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>CUST_NO</th>\n",
       "      <th>TPAY_MOTH_TR_AMT</th>\n",
       "      <th>TPAY_SEAN_TR_AMT</th>\n",
       "      <th>TPAY_MOTH_NET_TR_AMT</th>\n",
       "      <th>TPAY_SEAN_NET_TR_AMT</th>\n",
       "      <th>TPAY_MOTH_TR_CNT</th>\n",
       "      <th>TPAY_SEAN_TR_CNT</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0992cc717603736b7b043f02c1ad5e2c</td>\n",
       "      <td>53.172816</td>\n",
       "      <td>53.172816</td>\n",
       "      <td>53.172816</td>\n",
       "      <td>53.172816</td>\n",
       "      <td>5.0</td>\n",
       "      <td>5.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>f00c45baeebd4b470d7c80a27f9be91c</td>\n",
       "      <td>71.875414</td>\n",
       "      <td>80.862476</td>\n",
       "      <td>67.573147</td>\n",
       "      <td>66.836186</td>\n",
       "      <td>41.0</td>\n",
       "      <td>187.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>68f117a886d62d1c6552e46ce76960ac</td>\n",
       "      <td>49.152640</td>\n",
       "      <td>49.152640</td>\n",
       "      <td>49.152640</td>\n",
       "      <td>49.152640</td>\n",
       "      <td>5.0</td>\n",
       "      <td>5.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>117b295d90f57713eba1893a0cb0c46f</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>3.0</td>\n",
       "      <td>3.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>537a2db0ea78e139f5e24b844645dcc8</td>\n",
       "      <td>37.638853</td>\n",
       "      <td>59.154734</td>\n",
       "      <td>-37.499561</td>\n",
       "      <td>-58.657642</td>\n",
       "      <td>91.0</td>\n",
       "      <td>201.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                            CUST_NO  TPAY_MOTH_TR_AMT  TPAY_SEAN_TR_AMT  \\\n",
       "0  0992cc717603736b7b043f02c1ad5e2c         53.172816         53.172816   \n",
       "1  f00c45baeebd4b470d7c80a27f9be91c         71.875414         80.862476   \n",
       "2  68f117a886d62d1c6552e46ce76960ac         49.152640         49.152640   \n",
       "3  117b295d90f57713eba1893a0cb0c46f          0.000000          0.000000   \n",
       "4  537a2db0ea78e139f5e24b844645dcc8         37.638853         59.154734   \n",
       "\n",
       "   TPAY_MOTH_NET_TR_AMT  TPAY_SEAN_NET_TR_AMT  TPAY_MOTH_TR_CNT  \\\n",
       "0             53.172816             53.172816               5.0   \n",
       "1             67.573147             66.836186              41.0   \n",
       "2             49.152640             49.152640               5.0   \n",
       "3              0.000000              0.000000               3.0   \n",
       "4            -37.499561            -58.657642              91.0   \n",
       "\n",
       "   TPAY_SEAN_TR_CNT  \n",
       "0               5.0  \n",
       "1             187.0  \n",
       "2               5.0  \n",
       "3               3.0  \n",
       "4             201.0  "
      ]
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "tpay_test.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [],
   "source": [
    "ibtf_tpay_train = df_train_user.merge(ibtf_train,on='CUST_NO',how='left')\n",
    "ibtf_tpay_train = ibtf_tpay_train.merge(tpay_train,on='CUST_NO',how='left')\n",
    "\n",
    "ibtf_tpay_test = df_test_user.merge(ibtf_test,on='CUST_NO',how='left')\n",
    "ibtf_tpay_test = ibtf_tpay_test.merge(tpay_test,on='CUST_NO',how='left')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>CUST_NO</th>\n",
       "      <th>IBTF_MOTH_TR_AMT</th>\n",
       "      <th>IBTF_YEAR_TR_AMT</th>\n",
       "      <th>IBTF_MOTH_NET_TR_AMT</th>\n",
       "      <th>IBTF_YEAR_NET_TR_AMT</th>\n",
       "      <th>IBTF_MOTH_TR_AMT_IN</th>\n",
       "      <th>IBTF_YEAR_TR_AMT_IN</th>\n",
       "      <th>IBTF_MOTH_TR_CNT</th>\n",
       "      <th>IBTF_YEAR_TR_CNT</th>\n",
       "      <th>IBTF_MOTH_TR_CNT_IN</th>\n",
       "      <th>IBTF_YEAR_TR_CNT_IN</th>\n",
       "      <th>TPAY_MOTH_TR_AMT</th>\n",
       "      <th>TPAY_SEAN_TR_AMT</th>\n",
       "      <th>TPAY_MOTH_NET_TR_AMT</th>\n",
       "      <th>TPAY_SEAN_NET_TR_AMT</th>\n",
       "      <th>TPAY_MOTH_TR_CNT</th>\n",
       "      <th>TPAY_SEAN_TR_CNT</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>235e4e193124d8c55095cf3f0f0d8f35</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>3.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>f1b5ca32a8f7ef5430f5775c00ff3f60</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>51be6f380b408edeb7779b76e016dcd3</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>50.338974</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>18.245871</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>40.578453</td>\n",
       "      <td>3.0</td>\n",
       "      <td>7.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>0.00000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>3.0</td>\n",
       "      <td>3.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>ccd7e33ccbe7e9dd4246a2959f666c0a</td>\n",
       "      <td>36.491741</td>\n",
       "      <td>105.077335</td>\n",
       "      <td>36.491741</td>\n",
       "      <td>18.245871</td>\n",
       "      <td>36.491741</td>\n",
       "      <td>83.545232</td>\n",
       "      <td>5.0</td>\n",
       "      <td>33.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>31.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>069f48f51bf6be5bcbdc9af52bb20970</td>\n",
       "      <td>26.271167</td>\n",
       "      <td>83.960901</td>\n",
       "      <td>26.271167</td>\n",
       "      <td>83.960901</td>\n",
       "      <td>26.271167</td>\n",
       "      <td>83.960901</td>\n",
       "      <td>5.0</td>\n",
       "      <td>29.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>29.0</td>\n",
       "      <td>85.53248</td>\n",
       "      <td>120.322966</td>\n",
       "      <td>34.542456</td>\n",
       "      <td>-48.586268</td>\n",
       "      <td>143.0</td>\n",
       "      <td>325.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                            CUST_NO  IBTF_MOTH_TR_AMT  IBTF_YEAR_TR_AMT  \\\n",
       "0  235e4e193124d8c55095cf3f0f0d8f35          0.000000          0.000000   \n",
       "1  f1b5ca32a8f7ef5430f5775c00ff3f60               NaN               NaN   \n",
       "2  51be6f380b408edeb7779b76e016dcd3          0.000000         50.338974   \n",
       "3  ccd7e33ccbe7e9dd4246a2959f666c0a         36.491741        105.077335   \n",
       "4  069f48f51bf6be5bcbdc9af52bb20970         26.271167         83.960901   \n",
       "\n",
       "   IBTF_MOTH_NET_TR_AMT  IBTF_YEAR_NET_TR_AMT  IBTF_MOTH_TR_AMT_IN  \\\n",
       "0              0.000000              0.000000             0.000000   \n",
       "1                   NaN                   NaN                  NaN   \n",
       "2              0.000000             18.245871             0.000000   \n",
       "3             36.491741             18.245871            36.491741   \n",
       "4             26.271167             83.960901            26.271167   \n",
       "\n",
       "   IBTF_YEAR_TR_AMT_IN  IBTF_MOTH_TR_CNT  IBTF_YEAR_TR_CNT  \\\n",
       "0             0.000000               3.0               3.0   \n",
       "1                  NaN               NaN               NaN   \n",
       "2            40.578453               3.0               7.0   \n",
       "3            83.545232               5.0              33.0   \n",
       "4            83.960901               5.0              29.0   \n",
       "\n",
       "   IBTF_MOTH_TR_CNT_IN  IBTF_YEAR_TR_CNT_IN  TPAY_MOTH_TR_AMT  \\\n",
       "0                  3.0                  3.0               NaN   \n",
       "1                  NaN                  NaN               NaN   \n",
       "2                  3.0                  5.0           0.00000   \n",
       "3                  5.0                 31.0               NaN   \n",
       "4                  5.0                 29.0          85.53248   \n",
       "\n",
       "   TPAY_SEAN_TR_AMT  TPAY_MOTH_NET_TR_AMT  TPAY_SEAN_NET_TR_AMT  \\\n",
       "0               NaN                   NaN                   NaN   \n",
       "1               NaN                   NaN                   NaN   \n",
       "2          0.000000              0.000000              0.000000   \n",
       "3               NaN                   NaN                   NaN   \n",
       "4        120.322966             34.542456            -48.586268   \n",
       "\n",
       "   TPAY_MOTH_TR_CNT  TPAY_SEAN_TR_CNT  \n",
       "0               NaN               NaN  \n",
       "1               NaN               NaN  \n",
       "2               3.0               3.0  \n",
       "3               NaN               NaN  \n",
       "4             143.0             325.0  "
      ]
     },
     "execution_count": 28,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ibtf_tpay_train.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>CUST_NO</th>\n",
       "      <th>IBTF_MOTH_TR_AMT</th>\n",
       "      <th>IBTF_YEAR_TR_AMT</th>\n",
       "      <th>IBTF_MOTH_NET_TR_AMT</th>\n",
       "      <th>IBTF_YEAR_NET_TR_AMT</th>\n",
       "      <th>IBTF_MOTH_TR_AMT_IN</th>\n",
       "      <th>IBTF_YEAR_TR_AMT_IN</th>\n",
       "      <th>IBTF_MOTH_TR_CNT</th>\n",
       "      <th>IBTF_YEAR_TR_CNT</th>\n",
       "      <th>IBTF_MOTH_TR_CNT_IN</th>\n",
       "      <th>IBTF_YEAR_TR_CNT_IN</th>\n",
       "      <th>TPAY_MOTH_TR_AMT</th>\n",
       "      <th>TPAY_SEAN_TR_AMT</th>\n",
       "      <th>TPAY_MOTH_NET_TR_AMT</th>\n",
       "      <th>TPAY_SEAN_NET_TR_AMT</th>\n",
       "      <th>TPAY_MOTH_TR_CNT</th>\n",
       "      <th>TPAY_SEAN_TR_CNT</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2ea465c039dca553869709c9f07d3e98</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>114.980084</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>114.980084</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>114.980084</td>\n",
       "      <td>3.0</td>\n",
       "      <td>7.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>7.0</td>\n",
       "      <td>64.665441</td>\n",
       "      <td>91.352165</td>\n",
       "      <td>-64.546593</td>\n",
       "      <td>-87.261859</td>\n",
       "      <td>63.0</td>\n",
       "      <td>227.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>805a1b20b7655ae02ecb2b1a216df747</td>\n",
       "      <td>171.298472</td>\n",
       "      <td>341.295549</td>\n",
       "      <td>-102.540478</td>\n",
       "      <td>181.858523</td>\n",
       "      <td>125.446644</td>\n",
       "      <td>283.910957</td>\n",
       "      <td>19.0</td>\n",
       "      <td>139.0</td>\n",
       "      <td>7.0</td>\n",
       "      <td>53.0</td>\n",
       "      <td>105.805797</td>\n",
       "      <td>153.462964</td>\n",
       "      <td>105.805797</td>\n",
       "      <td>83.254131</td>\n",
       "      <td>9.0</td>\n",
       "      <td>15.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>f8c8c42606afa6e1ab4247c9d9903e79</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>82.068858</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>82.068858</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>82.068858</td>\n",
       "      <td>3.0</td>\n",
       "      <td>15.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>15.0</td>\n",
       "      <td>69.090108</td>\n",
       "      <td>94.382238</td>\n",
       "      <td>-2.955477</td>\n",
       "      <td>-6.394731</td>\n",
       "      <td>37.0</td>\n",
       "      <td>147.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>8e63b90522f7d0f89930c141b6d62ba3</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>e71614849f7148cd0d1876b58cf23f5b</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>8.325413</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>-8.325413</td>\n",
       "      <td>3.0</td>\n",
       "      <td>5.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                            CUST_NO  IBTF_MOTH_TR_AMT  IBTF_YEAR_TR_AMT  \\\n",
       "0  2ea465c039dca553869709c9f07d3e98          0.000000        114.980084   \n",
       "1  805a1b20b7655ae02ecb2b1a216df747        171.298472        341.295549   \n",
       "2  f8c8c42606afa6e1ab4247c9d9903e79          0.000000         82.068858   \n",
       "3  8e63b90522f7d0f89930c141b6d62ba3               NaN               NaN   \n",
       "4  e71614849f7148cd0d1876b58cf23f5b               NaN               NaN   \n",
       "\n",
       "   IBTF_MOTH_NET_TR_AMT  IBTF_YEAR_NET_TR_AMT  IBTF_MOTH_TR_AMT_IN  \\\n",
       "0              0.000000            114.980084             0.000000   \n",
       "1           -102.540478            181.858523           125.446644   \n",
       "2              0.000000             82.068858             0.000000   \n",
       "3                   NaN                   NaN                  NaN   \n",
       "4                   NaN                   NaN                  NaN   \n",
       "\n",
       "   IBTF_YEAR_TR_AMT_IN  IBTF_MOTH_TR_CNT  IBTF_YEAR_TR_CNT  \\\n",
       "0           114.980084               3.0               7.0   \n",
       "1           283.910957              19.0             139.0   \n",
       "2            82.068858               3.0              15.0   \n",
       "3                  NaN               NaN               NaN   \n",
       "4                  NaN               NaN               NaN   \n",
       "\n",
       "   IBTF_MOTH_TR_CNT_IN  IBTF_YEAR_TR_CNT_IN  TPAY_MOTH_TR_AMT  \\\n",
       "0                  3.0                  7.0         64.665441   \n",
       "1                  7.0                 53.0        105.805797   \n",
       "2                  3.0                 15.0         69.090108   \n",
       "3                  NaN                  NaN               NaN   \n",
       "4                  NaN                  NaN          0.000000   \n",
       "\n",
       "   TPAY_SEAN_TR_AMT  TPAY_MOTH_NET_TR_AMT  TPAY_SEAN_NET_TR_AMT  \\\n",
       "0         91.352165            -64.546593            -87.261859   \n",
       "1        153.462964            105.805797             83.254131   \n",
       "2         94.382238             -2.955477             -6.394731   \n",
       "3               NaN                   NaN                   NaN   \n",
       "4          8.325413              0.000000             -8.325413   \n",
       "\n",
       "   TPAY_MOTH_TR_CNT  TPAY_SEAN_TR_CNT  \n",
       "0              63.0             227.0  \n",
       "1               9.0              15.0  \n",
       "2              37.0             147.0  \n",
       "3               NaN               NaN  \n",
       "4               3.0               5.0  "
      ]
     },
     "execution_count": 29,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ibtf_tpay_test.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {},
   "outputs": [],
   "source": [
    "for ibtf_tpay in [ibtf_tpay_train,ibtf_tpay_test]:\n",
    "    ibtf_tpay['IBTF_TPAY_MOTH_TR_AMT'] = ibtf_tpay['IBTF_MOTH_TR_AMT'] + ibtf_tpay['TPAY_MOTH_TR_AMT']\n",
    "    ibtf_tpay['IBTF_TPAY_MOTH_NET_TR_AMT'] = ibtf_tpay['IBTF_MOTH_NET_TR_AMT'] + ibtf_tpay['TPAY_MOTH_NET_TR_AMT']\n",
    "    ibtf_tpay['IBTF_TPAY_MOTH_TR_CNT'] = ibtf_tpay['IBTF_MOTH_TR_CNT'] + ibtf_tpay['TPAY_MOTH_TR_CNT']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['CUST_NO', 'IBTF_MOTH_TR_AMT', 'IBTF_YEAR_TR_AMT',\n",
       "       'IBTF_MOTH_NET_TR_AMT', 'IBTF_YEAR_NET_TR_AMT', 'IBTF_MOTH_TR_AMT_IN',\n",
       "       'IBTF_YEAR_TR_AMT_IN', 'IBTF_MOTH_TR_CNT', 'IBTF_YEAR_TR_CNT',\n",
       "       'IBTF_MOTH_TR_CNT_IN', 'IBTF_YEAR_TR_CNT_IN', 'TPAY_MOTH_TR_AMT',\n",
       "       'TPAY_SEAN_TR_AMT', 'TPAY_MOTH_NET_TR_AMT', 'TPAY_SEAN_NET_TR_AMT',\n",
       "       'TPAY_MOTH_TR_CNT', 'TPAY_SEAN_TR_CNT', 'IBTF_TPAY_MOTH_TR_AMT',\n",
       "       'IBTF_TPAY_MOTH_NET_TR_AMT', 'IBTF_TPAY_MOTH_TR_CNT'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 31,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ibtf_tpay_test.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {},
   "outputs": [],
   "source": [
    "tmp_train2 = df_train_user.merge(ibtf_tpay_train,on='CUST_NO',how='left')\n",
    "tmp_test2 = df_test_user.merge(ibtf_tpay_test,on='CUST_NO',how='left')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {},
   "outputs": [],
   "source": [
    "tmp_train2 = tmp_train2.fillna(0)\n",
    "tmp_test2 = tmp_test2.fillna(0)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>CUST_NO</th>\n",
       "      <th>IBTF_MOTH_TR_AMT</th>\n",
       "      <th>IBTF_YEAR_TR_AMT</th>\n",
       "      <th>IBTF_MOTH_NET_TR_AMT</th>\n",
       "      <th>IBTF_YEAR_NET_TR_AMT</th>\n",
       "      <th>IBTF_MOTH_TR_AMT_IN</th>\n",
       "      <th>IBTF_YEAR_TR_AMT_IN</th>\n",
       "      <th>IBTF_MOTH_TR_CNT</th>\n",
       "      <th>IBTF_YEAR_TR_CNT</th>\n",
       "      <th>IBTF_MOTH_TR_CNT_IN</th>\n",
       "      <th>IBTF_YEAR_TR_CNT_IN</th>\n",
       "      <th>TPAY_MOTH_TR_AMT</th>\n",
       "      <th>TPAY_SEAN_TR_AMT</th>\n",
       "      <th>TPAY_MOTH_NET_TR_AMT</th>\n",
       "      <th>TPAY_SEAN_NET_TR_AMT</th>\n",
       "      <th>TPAY_MOTH_TR_CNT</th>\n",
       "      <th>TPAY_SEAN_TR_CNT</th>\n",
       "      <th>IBTF_TPAY_MOTH_TR_AMT</th>\n",
       "      <th>IBTF_TPAY_MOTH_NET_TR_AMT</th>\n",
       "      <th>IBTF_TPAY_MOTH_TR_CNT</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2ea465c039dca553869709c9f07d3e98</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>114.980084</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>114.980084</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>114.980084</td>\n",
       "      <td>3.0</td>\n",
       "      <td>7.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>7.0</td>\n",
       "      <td>64.665441</td>\n",
       "      <td>91.352165</td>\n",
       "      <td>-64.546593</td>\n",
       "      <td>-87.261859</td>\n",
       "      <td>63.0</td>\n",
       "      <td>227.0</td>\n",
       "      <td>64.665441</td>\n",
       "      <td>-64.546593</td>\n",
       "      <td>66.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>805a1b20b7655ae02ecb2b1a216df747</td>\n",
       "      <td>171.298472</td>\n",
       "      <td>341.295549</td>\n",
       "      <td>-102.540478</td>\n",
       "      <td>181.858523</td>\n",
       "      <td>125.446644</td>\n",
       "      <td>283.910957</td>\n",
       "      <td>19.0</td>\n",
       "      <td>139.0</td>\n",
       "      <td>7.0</td>\n",
       "      <td>53.0</td>\n",
       "      <td>105.805797</td>\n",
       "      <td>153.462964</td>\n",
       "      <td>105.805797</td>\n",
       "      <td>83.254131</td>\n",
       "      <td>9.0</td>\n",
       "      <td>15.0</td>\n",
       "      <td>277.104269</td>\n",
       "      <td>3.265319</td>\n",
       "      <td>28.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>f8c8c42606afa6e1ab4247c9d9903e79</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>82.068858</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>82.068858</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>82.068858</td>\n",
       "      <td>3.0</td>\n",
       "      <td>15.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>15.0</td>\n",
       "      <td>69.090108</td>\n",
       "      <td>94.382238</td>\n",
       "      <td>-2.955477</td>\n",
       "      <td>-6.394731</td>\n",
       "      <td>37.0</td>\n",
       "      <td>147.0</td>\n",
       "      <td>69.090108</td>\n",
       "      <td>-2.955477</td>\n",
       "      <td>40.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>8e63b90522f7d0f89930c141b6d62ba3</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>e71614849f7148cd0d1876b58cf23f5b</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>8.325413</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>-8.325413</td>\n",
       "      <td>3.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                            CUST_NO  IBTF_MOTH_TR_AMT  IBTF_YEAR_TR_AMT  \\\n",
       "0  2ea465c039dca553869709c9f07d3e98          0.000000        114.980084   \n",
       "1  805a1b20b7655ae02ecb2b1a216df747        171.298472        341.295549   \n",
       "2  f8c8c42606afa6e1ab4247c9d9903e79          0.000000         82.068858   \n",
       "3  8e63b90522f7d0f89930c141b6d62ba3          0.000000          0.000000   \n",
       "4  e71614849f7148cd0d1876b58cf23f5b          0.000000          0.000000   \n",
       "\n",
       "   IBTF_MOTH_NET_TR_AMT  IBTF_YEAR_NET_TR_AMT  IBTF_MOTH_TR_AMT_IN  \\\n",
       "0              0.000000            114.980084             0.000000   \n",
       "1           -102.540478            181.858523           125.446644   \n",
       "2              0.000000             82.068858             0.000000   \n",
       "3              0.000000              0.000000             0.000000   \n",
       "4              0.000000              0.000000             0.000000   \n",
       "\n",
       "   IBTF_YEAR_TR_AMT_IN  IBTF_MOTH_TR_CNT  IBTF_YEAR_TR_CNT  \\\n",
       "0           114.980084               3.0               7.0   \n",
       "1           283.910957              19.0             139.0   \n",
       "2            82.068858               3.0              15.0   \n",
       "3             0.000000               0.0               0.0   \n",
       "4             0.000000               0.0               0.0   \n",
       "\n",
       "   IBTF_MOTH_TR_CNT_IN  IBTF_YEAR_TR_CNT_IN  TPAY_MOTH_TR_AMT  \\\n",
       "0                  3.0                  7.0         64.665441   \n",
       "1                  7.0                 53.0        105.805797   \n",
       "2                  3.0                 15.0         69.090108   \n",
       "3                  0.0                  0.0          0.000000   \n",
       "4                  0.0                  0.0          0.000000   \n",
       "\n",
       "   TPAY_SEAN_TR_AMT  TPAY_MOTH_NET_TR_AMT  TPAY_SEAN_NET_TR_AMT  \\\n",
       "0         91.352165            -64.546593            -87.261859   \n",
       "1        153.462964            105.805797             83.254131   \n",
       "2         94.382238             -2.955477             -6.394731   \n",
       "3          0.000000              0.000000              0.000000   \n",
       "4          8.325413              0.000000             -8.325413   \n",
       "\n",
       "   TPAY_MOTH_TR_CNT  TPAY_SEAN_TR_CNT  IBTF_TPAY_MOTH_TR_AMT  \\\n",
       "0              63.0             227.0              64.665441   \n",
       "1               9.0              15.0             277.104269   \n",
       "2              37.0             147.0              69.090108   \n",
       "3               0.0               0.0               0.000000   \n",
       "4               3.0               5.0               0.000000   \n",
       "\n",
       "   IBTF_TPAY_MOTH_NET_TR_AMT  IBTF_TPAY_MOTH_TR_CNT  \n",
       "0                 -64.546593                   66.0  \n",
       "1                   3.265319                   28.0  \n",
       "2                  -2.955477                   40.0  \n",
       "3                   0.000000                    0.0  \n",
       "4                   0.000000                    0.0  "
      ]
     },
     "execution_count": 34,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.set_option('display.max_columns',None)\n",
    "tmp_test2.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {},
   "outputs": [],
   "source": [
    "tmp_train2.to_csv('../fea/train_combine.csv',index=False)\n",
    "tmp_test2.to_csv('../fea/test_combine.csv',index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.8.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}
